/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.andersen.database.dao;

import com.andersen.database.entity.OfficeEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import org.springframework.stereotype.Component;

/**
 *
 * @author Corvus 11
 */
@Component
public class OfficeDao extends AbstractDao{
    	
	public List<OfficeEntity> getAllObjects() {
            
            LOG.info("Getting office list");
            
            ArrayList<OfficeEntity> officeList = new ArrayList<OfficeEntity>();
            
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            
            try
            {
                conn = dataSource.getConnection();
                st = conn.prepareStatement("SELECT * FROM office WHERE office.deleted IS NULL ORDER BY name;");
                rs = st.executeQuery();
                if (rs.first()) {
                    do {
                        OfficeEntity ent = new OfficeEntity();
                        ent.setId(rs.getLong("id"));
                        ent.setAddress(rs.getString("address"));
                        ent.setName(rs.getString("name"));
                        ent.setLocation(rs.getString("location"));
                        ent.setManagerId(rs.getLong("manager_id"));
                        ent.setDeleted(rs.getBoolean("deleted"));
                        officeList.add(ent);
                    } while(rs.next());
                }
            } catch(SQLException exc) {
              LOG.error("Error with getting office list", exc);
            } finally {
                safeClose(rs);
                safeClose(st);
                safeClose(conn);
            }
            
            return officeList;
        }

    // Добавление офиса
    public void addOffice(String name,String location,String address,long managerId){

        LOG.info("OfficeDao.addOffice()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO equipment.office(name,location,address,manager_id) ");
        sql.append("VALUES (?,?,?,?);");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, name);
            statement.setString(2, location);
            statement.setString(3,address);
            statement.setLong(4,managerId);
            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.addNewComponentType()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

    }

    // Редактирование офиса
    public void editOffice(long id,String newName,String newLocation,String newAddress,long newManagerId){

        LOG.info("OfficeDao.editOffice()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE equipment.office ");
        sql.append("SET office.name = ?, ");
        sql.append("office.location = ?, ");
        sql.append("office.address = ?, ");
        sql.append("office.manager_id = ? ");
        sql.append("WHERE office.id = ?;");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, newName);
            statement.setString(2, newLocation);
            statement.setString(3, newAddress);
            statement.setLong(4, newManagerId);
            statement.setLong(5, id);
            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error OfficeDao.editOffice()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
    }

    // Удаление офиса
    public void removeOffice(long id){

        // пометка что офис удалён
        long deleted = 1;

        LOG.info("OfficeDao.removeOffice()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE equipment.office ");
        sql.append("SET office.deleted = ? ");
        sql.append("WHERE office.id = ?;");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, deleted);
            statement.setLong(2, id);
            statement.executeUpdate();

        }catch(SQLException exc) {
            LOG.error("Error OfficeDao.removeOffice()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
    }

    // Получаем id менеджера по id офиса
    public long getIdManager(long idOffice){

        long managerId = 0;

        LOG.info("EmployeeDao.getIdManager()");

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT office.manager_id ");
        sql.append("FROM equipment.office ");
        sql.append("WHERE office.id = ?;");

        try{
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setLong(1, idOffice);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
               managerId = resultSet.getLong("manager_id");
            }

        }catch(SQLException exc) {
            LOG.error("Error ComponentTypeDao.getIdManager()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }

        return managerId;
    }

}
